package com.ruoyi.system.service.impl;

import java.io.*;
import java.util.*;

import com.alibaba.fastjson2.JSONObject;
import com.ruoyi.common.core.domain.entity.SysDictType;
import com.ruoyi.system.domain.AllCustomsCodes;
import com.ruoyi.system.domain.DataMatchingLs;
import com.ruoyi.system.domain.DataMatchingLsSec;
import com.ruoyi.system.mapper.AllCustomsCodesMapper;
import com.ruoyi.system.mapper.SysDictTypeMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ruoyi.system.mapper.DataMatchingMapper;
import com.ruoyi.system.domain.DataMatching;
import com.ruoyi.system.service.IDataMatchingService;

/**
 * 数据匹配Service业务层处理
 * 
 * @author ruoyi
 * @date 2025-02-06
 */
@Service
public class DataMatchingServiceImpl implements IDataMatchingService 
{
    @Autowired
    private DataMatchingMapper dataMatchingMapper;
    @Autowired
    private AllCustomsCodesMapper allCustomsCodesMapper;
    @Autowired
    private SysDictTypeMapper sysDictTypeMapper;

    /**
     * 查询数据匹配
     * 
     * @param id 数据匹配主键
     * @return 数据匹配
     */
    @Override
    public DataMatching selectDataMatchingById(String id)
    {
        return dataMatchingMapper.selectDataMatchingById(id);
    }

    /**
     * 查询数据匹配列表
     * 
     * @param dataMatching 数据匹配
     * @return 数据匹配
     */
    @Override
    public List<DataMatching> selectDataMatchingList(DataMatching dataMatching)
    {
        return dataMatchingMapper.selectDataMatchingList(dataMatching);
    }

    /**
     * 新增数据匹配
     * 
     * @param dataMatching 数据匹配
     * @return 结果
     */
    @Override
    public int insertDataMatching(DataMatching dataMatching)
    {
        return dataMatchingMapper.insertDataMatching(dataMatching);
    }

    /**
     * 新增数据匹配
     *
     * @param dataMatching 数据匹配
     * @return 结果
     */
    @Override
    public int insertDataMatchingLs(DataMatching dataMatching)
    {
        return dataMatchingMapper.insertDataMatchingLs(dataMatching);
    }

    /**
     * 修改数据匹配
     * 
     * @param dataMatching 数据匹配
     * @return 结果
     */
    @Override
    public int updateDataMatching(DataMatching dataMatching)
    {
        return dataMatchingMapper.updateDataMatching(dataMatching);
    }

    /**
     * 批量删除数据匹配
     * 
     * @param ids 需要删除的数据匹配主键
     * @return 结果
     */
    @Override
    public int deleteDataMatchingByIds(String[] ids)
    {
        return dataMatchingMapper.deleteDataMatchingByIds(ids);
    }

    @Override
    public int deleteDataMatchingData() {
        return dataMatchingMapper.deleteDataMatchingLs();
    }


    /**
     * 删除数据匹配信息
     * 
     * @param id 数据匹配主键
     * @return 结果
     */
    @Override
    public int deleteDataMatchingById(String id)
    {
        return dataMatchingMapper.deleteDataMatchingById(id);
    }

    public JSONObject importExcelData(InputStream inputStream, String type) throws Exception {
        SysDictType dictType = sysDictTypeMapper.selectDictTypeById(11L);
        dictType.setStatus("0");
        sysDictTypeMapper.updateDictType(dictType);
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);  // 获取第一个工作表
        JSONObject res = new JSONObject();
        int resNum = 0;
        int errorNum = 0;
        // 清空表
        dataMatchingMapper.deleteDataMatchingLs();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            SysDictType sysDictType = sysDictTypeMapper.selectDictTypeById(11L);
            if (sysDictType == null && sysDictType.getStatus().equals("1")) {
                dataMatchingMapper.deleteDataMatchingLs();
                break;
            }
            Row row = sheet.getRow(i);
            if (getCellValue(row, 0).isEmpty() && getCellValue(row, 1).isEmpty() && getCellValue(row, 2).isEmpty() && getCellValue(row, 3).isEmpty() && getCellValue(row, 4).isEmpty() && getCellValue(row, 5).isEmpty() && getCellValue(row, 6).isEmpty()) {
                continue;
            }
            DataMatching commodityInfo = new DataMatching();
            String id = UUID.randomUUID().toString().replace("-", "");
            commodityInfo.setId(id);
            commodityInfo.setEori(getCellValue(row, 0));
            commodityInfo.setClientsDescription(getCellValue(row, 1));
            commodityInfo.setClientsDescriptionInChinese(getCellValue(row, 2));
            commodityInfo.setMaterial(getCellValue(row, 3));
            commodityInfo.setClientsHsCode(getCellValue(row, 4));
            commodityInfo.setAsin(getCellValue(row, 5));
            commodityInfo.setCommodity(getCellValue(row, 6));
            commodityInfo.setHsCode(getCellValue(row, 7));
            commodityInfo.setThirdCountryDuty(getCellValue(row, 8));

            if (commodityInfo.getEori().isEmpty() && commodityInfo.getClientsDescription().isEmpty() && commodityInfo.getClientsDescriptionInChinese().isEmpty() && commodityInfo.getMaterial().isEmpty() && commodityInfo.getClientsHsCode().isEmpty() && commodityInfo.getAsin().isEmpty()) {
                continue;
            }
            if (type!=null && type.equals("1")){
                if (dataMatchingMapper.insertDataMatchingLs(commodityInfo) > 0){
                    resNum++;
                }else{
                    errorNum++;
                }
            } else {
                if (dataMatchingMapper.insertDataMatching(commodityInfo) > 0){
                    resNum++;
                }else {
                    errorNum++;
                }
            }
        }

//        if (type != null && type.equals("1")) {
//            dataPro(dataMatchingMapper.selectDataMatchingLsList());
//        }
        res.put("code", 200);
        res.put("successNum",resNum);
        res.put("errorNum" , errorNum);
        return res;
    }

    @Override
    public JSONObject importExcelData() throws Exception {
        return null;
    }

    @Override
    public int updateDataMatchingLs(DataMatchingLs dataMatching) {
        int i = dataMatchingMapper.updateDataMatchingLs(dataMatching);
        noticePy();
        return i;
    }


    // 自定义读取单元格值的辅助方法
    private String getCellValue(Row row, int cellIndex) {
        Cell cell = row.getCell(cellIndex);
        if (cell == null) {
            return "";  // 如果单元格为空，返回空字符串
        }

        DataFormatter dataFormatter = new DataFormatter();
        String cellValue = dataFormatter.formatCellValue(cell);  // 以字符串形式读取单元格的值
        return cellValue.trim();
    }

    // A模式匹配方法。
    // list1一行数据  book11数据库
    private DataMatchingLs planA(DataMatchingLs dataMatching, List<DataMatching> dataMatchingList) {

                // 创建一个副本，以免修改原始数据
                List<DataMatchingLs> book12 = new ArrayList<>();

                // 遍历 book11，计算匹配度并存储
                for (DataMatching row : dataMatchingList) {
                    DataMatchingLs newDataMatching = dataMatching.clone();
                    double match2 = row.getClientsDescriptionInChinese().equals(dataMatching.getClientsDescriptionInChinese()) ? 0.3 : 0;
                    double match1 = row.getClientsDescription().equals(dataMatching.getClientsDescription()) ? 0.25 : 0;
                    double match3 = row.getMaterial().equals(dataMatching.getMaterial()) ? 0.2 : 0;
                    double match4 = row.getClientsHsCode().equals(dataMatching.getClientsHsCode()) ? 0.15 : 0;
                    double match5 = row.getAsin().equals(dataMatching.getAsin()) ? 0.1 : 0;

                    // 计算总匹配度
                    double totalMatch = match1 + match2 + match3 + match4 + match5;

                    newDataMatching.setEori(row.getEori());
                    newDataMatching.setClientsDescription(row.getClientsDescription());
                    newDataMatching.setMaterial(row.getMaterial());
                    newDataMatching.setClientsHsCode(row.getClientsHsCode());
                    newDataMatching.setAsin(row.getAsin());
                    newDataMatching.setClientsDescriptionInChinese(row.getClientsDescriptionInChinese());
                    newDataMatching.setMatchingDegree(totalMatch);
                    newDataMatching.setMatchPattern("A");
                    newDataMatching.setThirdCountryDuty(String.valueOf(match3));
                    newDataMatching.setHsCode(row.getHsCode());
                    newDataMatching.setCommodity(row.getCommodity());
                    book12.add(newDataMatching);
                }

                // 根据匹配度排序
                book12.sort((row1, row2) -> Double.compare( row2.getMatchingDegree(), row1.getMatchingDegree()));

                // 选择匹配度最高的一行
                dataMatchingMapper.updateDataMatchingLs(book12.get(0));
                return book12.get(0);
            }

    // B模式匹配方法
    private DataMatchingLs planB(DataMatchingLs dataMatching, List<DataMatching> dataMatchingList) {
        // 创建一个副本，以免修改原始数据
        List<DataMatchingLs> book12 = new ArrayList<>();

        // 遍历 book11，计算匹配度并存储
        for (DataMatching row : dataMatchingList) {
            DataMatchingLs newDataMatching = dataMatching.clone();
            double match2 = row.getClientsDescriptionInChinese().equals(dataMatching.getClientsDescriptionInChinese()) ? 0.3 : 0;
            double match1 = row.getClientsDescription().equals(dataMatching.getClientsDescription()) ? 0.25 : 0;
            double match3 = row.getMaterial().equals(dataMatching.getMaterial()) ? 0.2 : 0;
            double match4 = row.getClientsHsCode().equals(dataMatching.getClientsHsCode()) ? 0.15 : 0;
            double match5 = row.getAsin().equals(dataMatching.getAsin()) ? 0.1 : 0;

            // 计算总匹配度
            double totalMatch = match1 + match2 + match3 + match4 + match5;

            newDataMatching.setEori(row.getEori());
            newDataMatching.setClientsDescription(row.getClientsDescription());
            newDataMatching.setMaterial(row.getMaterial());
            newDataMatching.setClientsHsCode(row.getClientsHsCode());
            newDataMatching.setAsin(row.getAsin());
            newDataMatching.setClientsDescriptionInChinese(row.getClientsDescriptionInChinese());
            newDataMatching.setMatchingDegree(totalMatch);
            newDataMatching.setMatchPattern("B");
            newDataMatching.setThirdCountryDuty(String.valueOf(match3));
            newDataMatching.setHsCode(row.getHsCode());
            newDataMatching.setCommodity(row.getCommodity());

            book12.add(newDataMatching);
        }

        // 根据匹配度排序
        book12.sort((row1, row2) -> Double.compare( row2.getMatchingDegree(), row1.getMatchingDegree()));

        if(book12.get(0).getMatchingDegree() >= 0.6){
            book12.get(0).setMatchPattern("B");
        }

        return book12.get(0);
    }

    // C模式匹配方法
    private DataMatchingLs planC(DataMatchingLs dataMatching, List<AllCustomsCodes> allCustomsCodes) {
        String asin = dataMatching.getAsin();
        List<String> asins = new ArrayList<>();
        int length = asin.length();
        asins.add(asin);
        if (length > 9){
            asins.add(asin.substring(0, 9));
        }
        if (length > 8){
            asins.add(asin.substring(0, 8));
        }
        if (length > 7){
            asins.add(asin.substring(0, 7));
        }
        if (length > 6){
            asins.add(asin.substring(0, 6));
        }

        for (String asin1 : asins) {
            if (asin1.length() == 6) {
                for (AllCustomsCodes allCustomsCode : allCustomsCodes) {
                    if (allCustomsCode.getCommodityCode().startsWith(asin1) && allCustomsCode.getStatus().equals("0")) {
                        dataMatching.setMatchPattern("C");
                        return dataMatching;
                    }else if (allCustomsCode.getCommodityCode().startsWith(asin1)){
                        return dataMatching;
                    }

                }
            }
        }

        return dataMatching;
    }

    public void dataPro() {
        List<DataMatchingLs> dataMatchings = dataMatchingMapper.selectDataMatchingLsList();
        System.out.println(dataMatchings);
        //查询所有建议可用归类的list 获取Commodity code 和。status（是否优先）
        AllCustomsCodes param = new AllCustomsCodes();
        param.setType("2");
        List<AllCustomsCodes> allCustomsCodes = allCustomsCodesMapper.selectAllCustomsCodesList(param);
//        List<DataMatching> allDataMatchings = dataMatchingMapper.selectDataMatchingList(new DataMatching());
        for(DataMatchingLs dataMatching : dataMatchings) {
            DataMatching paramData = new DataMatching();
            paramData.setEori(dataMatching.getEori());
            List<DataMatching> dataMatchings1 = dataMatchingMapper.selectDataMatchingList(paramData);
            if (!dataMatchings1.isEmpty()) {
                DataMatchingLs resData = planA(dataMatching, dataMatchings1);
                dataMatchingMapper.updateDataMatchingLs(resData);
                if (resData != null && resData.getMatchingDegree() < 0.6) {
                    DataMatchingLs dataMatching1 = planB(dataMatching, dataMatchings1);
                    dataMatchingMapper.updateDataMatchingLs(dataMatching1);
                }
                if (resData != null && resData.getMatchingDegree() < 0.6){
                    DataMatchingLs dataMatching1 = planC(dataMatching, allCustomsCodes);
                    dataMatching1.setMatchPattern("C");
                    dataMatchingMapper.updateDataMatchingLs(dataMatching1);
                }
            }
        }
    }

    public List<DataMatchingLs> noticePy(){
        AllCustomsCodes param = new AllCustomsCodes();
        param.setType("1");
        param.setGroupBy("clients_hs_code");
        List<AllCustomsCodes> groupByClientsHsCode = allCustomsCodesMapper.selectAllCustomsCodesListGroupBy(param);
        //全部
        param.setType("0");
        List<AllCustomsCodes> allCustomsCodes1 = allCustomsCodesMapper.selectAllCustomsCodesListGroupBy(param);
        //匹配数据
        List<DataMatchingLs> dataMatchings = dataMatchingMapper.selectDataMatchingLsList();

        //循环book2的列表
        for (DataMatchingLs dataMatching : dataMatchings) {
            if (dataMatching != null){
                String clientsHsCode = dataMatching.getClientsHsCode();
                //匹配book1
                for (AllCustomsCodes  allcode :  allCustomsCodes1){
                    if(!clientsHsCode.isEmpty() && clientsHsCode.equals(allcode.getCommodityCode())){
                        dataMatching.setClientsHsCode(allcode.getCommodityCode());
                        dataMatching.setCustomsCode("VALID");
                    } else {
                        dataMatching.setCustomsCode("INVALID");
                    }
                }
                //匹配book3
                for (AllCustomsCodes allCustomsCodes: groupByClientsHsCode){
                    if (allCustomsCodes!=null){
                        if (!clientsHsCode.isEmpty() && clientsHsCode.equals(allCustomsCodes.getCommodityCode())){
                            if ("".equals(allCustomsCodes.getRemark()) || null == allCustomsCodes.getRemark()){
                                dataMatching.setRemark("");
                            }else{
                                dataMatching.setRemark("原海关编码 " + allCustomsCodes.getRemark());
                            }
                        }else{
                            if (allCustomsCodes.getRemark() != null && allCustomsCodes.getRemark().equals("VALID")){
                                dataMatching.setRemark("原海关编码归类不正确，请重新确认");
                            }else{
                                dataMatching.setRemark("原海关编码无效，请重新确认");
                            }
                        }
                        if (dataMatching.getHsCode()!=null && dataMatching.getHsCode().equals(allCustomsCodes.getCommodityCode())){
                            if (allCustomsCodes.getRemark().isEmpty()){
                                dataMatching.setRemark("");
                            }else{
                                dataMatching.setRemark("产品海关编码 " + allCustomsCodes.getRemark());
                            }
                        }
                    }
                }
            }

        }

        for (DataMatchingLs dataMatching : dataMatchings) {
            String clientsHsCode = dataMatching.getClientsHsCode();
            String hsCode = dataMatching.getHsCode();
            String str2 = "";
            if (clientsHsCode != null && clientsHsCode.equals(hsCode)){
                dataMatching.setCustomsGood(hsCode);
            }else {
                String value1 = clientsHsCode!=null && clientsHsCode.isEmpty() ? clientsHsCode : "";
                String value2 = hsCode!=null &&hsCode.isEmpty() ? hsCode : "";

                if (value1.length() > 0 && value2.length() > 0) {
                    dataMatching.setCustomsGood(value1 + " " + value2);
                }else{
                    dataMatching.setCustomsGood(value1 + value2);
                }
            }

            if ("VALID".equals(dataMatching.getCustomsCode())){
                str2 = "原海关编码归类不正确，请重新确认";
            }else{
                str2 = "原海关编码无效，请重新确认";
            }
            dataMatching.setRemark(str2);

        }

        for (DataMatchingLs dataMatching : dataMatchings) {
            dataMatchingMapper.updateDataMatchingLs(dataMatching);
        }




    return dataMatchings;
    }

    @Override
    public List<DataMatchingLs> selectDataMatchingListLs(DataMatching dataMatching) {
        return dataMatchingMapper. selectDataMatchingListLs(dataMatching);
    }

    @Override
    public List<DataMatchingLsSec> selectDataMatchingListLsSec(DataMatching dataMatching) {
        return dataMatchingMapper.selectDataMatchingListLsSec(dataMatching);
    }

    @Override
    public int deleteDataMatchingLsByIds(String[] ids) {
        return dataMatchingMapper.deleteDataMatchingLsByIds(ids);
    }

    @Override
    public void dataProFir() throws Exception {
        dataPro();
    }

    @Override
    public void quitUpload() {
        SysDictType dictType = sysDictTypeMapper.selectDictTypeById(11L);
        dictType.setStatus("1");
        sysDictTypeMapper.updateDictType(dictType);
    }

}
